﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: December 20, 2015
-- Description:	Takes a ShortRelationshipName and a permission number and returns all higher-level 
--				SuperRelationships
-- =============================================
CREATE FUNCTION [dbo].[ViewSuperRelationships] 
(
	-- Just the ShortRelatioshipName
	@ShortRelationshipName nvarchar(50)
)
RETURNS 
@ReturnTable TABLE 
(
	ShortRelationshipName nvarchar(50),
	SubRelationshipName nvarchar(50)
)
AS
BEGIN
	DECLARE @SuperRelationship nvarchar(50),@MyRowCount int

	SELECT @SuperRelationship = SuperRelationship FROM DefinedEntityRelationships
	WHERE ShortRelationshipName = @ShortRelationshipName AND NOT SuperRelationship IS NULL

	IF NOT @SuperRelationship IS NULL BEGIN

		INSERT INTO @ReturnTable(ShortRelationshipName,SubRelationshipName)
		VALUES(@SuperRelationship,';'+@ShortRelationshipName+';')

		-- Recursive 
		INSERT INTO @ReturnTable(ShortRelationshipName,SubRelationshipName)
		SELECT ShortRelationshipName,SubRelationshipName + @ShortRelationshipName + ';'
		FROM ViewSuperRelationships(@SuperRelationship)

		IF @@ROWCOUNT = 1
			DELETE FROM @ReturnTable WHERE ShortRelationshipName = @SuperRelationship

	END

	RETURN 
END